import pandas as pd
import time
import os

excel = r'./执行价格导入2.信阳市规范整合放射检查类医疗服务价格项目.xlsx'
exec_index = 2

exec_price = ['TOP3_PRICE', 'THREE_LEVEL_PRICE', 'TWO_A_PRICE', 'TWO_LEVEL_PRICE', 'ONE_LEVEL_PRICE']

def format_field(name):
    """
    格式化列名，将其转换为规范的字段名格式
    :param name: 原始列名
    :return: 格式化后的字段名
    """
    if "财务分类代码" in name:
        return "FINANCE_CLASS"
    if "项目编码" in name:
        return "MED_LIST_CODE"
    if "项目名称" in name:
        return "SRVC_NAME"
    if "使用说明" in name:
        return "USE_INS"
    if "服务产出" in name:
        return "SRVC_OUT"
    if "价格构成" in name:
        return "PRICE_COMPOSE"
    if "加收项" in name:
        return "ADD_ITEM"
    if "扩展项" in name:
        return "EXTEND_ITEM"
    if "计价单位" in name:
        return "BILL_UNIT"
    if "三级甲等" in name or "三甲" in name:
        return "TOP3_PRICE"
    if "三级非甲等" in name or "非三甲" in name:
        return "THREE_LEVEL_PRICE"
    if "二级甲等" in name:
        return "TWO_A_PRICE"
    if "二级非甲等" in name:
        return "TWO_LEVEL_PRICE"
    if "一级" in name:
        return "ONE_LEVEL_PRICE"
    if "计价说明" in name:
        return "SRVC_ISTR"
    if "支付类别" in name:
        return "PAY_TYPE"
    if "备注" in name:
        return "TXT_REM"
    return name

def deal_col(df, file_name):
    # 添加列
    df['ID'] = int(time.time() * 1000) + df.index + 1
    df['ORG_ID'] = 2
    df['LOCAL_MED_LIST_CODE'] = df.get('MED_LIST_CODE', None)
    df['LOCAL_SRVC_NAME'] = df.get('SRVC_NAME', None)
    df['BILL_UNIT_NAME'] = df.get('BILL_UNIT', None)
    df['CLASS_AB'] = df.get('PAY_TYPE', None)
    df['CREATE_DATE'] = time.strftime('%d/%m/%Y %H:%M:%S', time.localtime())
    df['CREATE_BY'] = file_name
    df['UPDATE_DATE'] = time.strftime('%d/%m/%Y %H:%M:%S', time.localtime()) 
    df['UPDATE_BY'] = file_name
    df['EXEC_PRICE'] = df[exec_price[exec_index]]

    # 移除列名中包含‘unnamed‘的列
    df.drop(df.columns[df.columns.str.contains('Unnamed', case=False)], axis=1, inplace=True)

def deal(work_path):
    try:
        # 遍历./item 目录下的所有excel，执行处理
        for file in os.listdir(work_path):
            if file.endswith('.xlsx'):
                excel = os.path.join(work_path, file)
                df = pd.read_excel(excel, header=[1,2], dtype=str)
                # 将两行列名合并为一行
                df.columns = df.columns.map(lambda x: f"{x[0]}_{x[1]}")
                # 处理列名
                df = df.rename(lambda x: format_field(x), axis=1)
                deal_col(df, file.replace('.xlsx', ''))
                df.to_json(f'{work_path}/{file.replace(".xlsx", ".json")}', orient='records', indent=2)
    except Exception as e:
        print(f"处理 Excel 文件出错: {e}")

if __name__ == '__main__':
    deal(r'./item')